package database;


import domain.Paper;
import domain.User;
import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.faces.model.SelectItem;

public class DBController {

    private static String dbuser = "root";
    private static String pass = "root";
    private static String dbUrl = "jdbc:mysql://localhost:3306/cnfrnc";
    protected Connection connection;
    private String writerCounter;

    public Connection getConnection() {
        return connection;
    }

                
    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    public DBController() {
        try 
        {
            try {
                System.out.println("driver i yukleyecek......");
                Class.forName("com.mysql.jdbc.Driver");
                System.out.println("driver i yukledi......");
            } catch (ClassNotFoundException ex) 
            {
                System.out.println("driver i yukleyemedi......");
                Logger.getLogger(DBController.class.getName()).log(Level.SEVERE, null, ex);
            }
            System.out.println("connection yaratilcak......");
            this.connection = DriverManager.getConnection(dbUrl, dbuser, pass);
            System.out.println("connection yaratildi......");
            
        } 
        catch (SQLException exc) 
        {
            System.out.println("connection yaratilamadi......");
        }
    }

    public int getUserId(String userNick) throws SQLException
    {
        Statement s = connection.createStatement();
        ResultSet rs;
        rs = s.executeQuery(" SELECT * FROM USER WHERE UserNick =' "+userNick+" ' " );
        int id = rs.getInt("UserId");
        
        System.out.printf("...........dbcontroller 59  %d\n",id);
        return id;
    }


    public ResultSet loginValidate(String uname, String userpass) throws SQLException {
        Statement s = connection.createStatement();
        s.executeQuery("SELECT * FROM user WHERE UserNick = '" + uname + "' AND UserPass = '" + userpass + "'");

        return s.getResultSet();
    }

    public void saveUser(User u) throws SQLException {
        
        System.out.printf("u.getUserNick():%s \n",u.getUserNick());
        
        String insertUserSQL = "INSERT INTO USER"
		+ "(USERID, USERNICK, USERTYPEID, USERWORKINGPLACE, USERINTERESTS,USERNAME,USERPASS,USEREMAIL) VALUES"
		+ "(?,?,?,?,?,?,?,?)";
        PreparedStatement ps = connection.prepareStatement(insertUserSQL);

        ps.setInt(1, u.getId());
        ps.setString(2, u.getUserNick());
        ps.setInt(3, u.getType());
        ps.setString(4,u.getWorkingPlace());
        ps.setString(5, u.getInterest());
        ps.setString(6, u.getName());
        ps.setString(7, u.getPassword());
        ps.setString(8, u.getEmail());

        ps.executeUpdate();
        
                
 
    }
    
    public void savePaper(Paper p) throws SQLException {
        System.out.printf("u.getWriter_ID():%d \n",p.getWriter_ID());

        String insertUserSQL = "INSERT INTO PAPER"
		+ "(writer_ID, JUDGE_ID, TITLE, SUMMARY, KEYWORDS,TOPIC,GRADE,ISSUCCESFULL,COMMENTS,SCIENTIFIC_VALUE,ID) VALUES"
		+ "(?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement ps = connection.prepareStatement(insertUserSQL);

        ps.setInt(1, p.getWriter_ID());
        ps.setInt(2,p.getJudge_ID());
        ps.setString(3, p.getTitle());
        ps.setString(4,p.getSummary());
        ps.setString(5, p.getKeywords());
        ps.setString(6, p.getTopic());
        ps.setInt(7, p.getGrade());
        ps.setInt(8, p.getIssuccesfull());
        ps.setString(9, p.getComments());
        ps.setInt(10, p.getScientificValue());
        ps.setInt(11, p.getPaper_ID());

        ps.executeUpdate();
        
        connection.close();
    }
    
    public void saveChangesFromJudge(Paper p) throws SQLException {
        System.out.printf("P.getComments():%s \n",p.getComments());
    
        String query = "UPDATE PAPER SET COMMENTS='"+p.getComments()+"', GRADE='"+p.getGrade()+"'  WHERE ID='"+p.getPaper_ID()+"'";
        PreparedStatement ps = connection.prepareStatement(query);
        ps.executeUpdate();
        
        connection.close();
    }
    
    
    public String getFullName(int userId) throws SQLException {
        Statement s =connection.createStatement();
        ResultSet rs = s.executeQuery("SELECT * FROM USER WHERE UserId = "+userId+"");
                if(rs.next()){
                    return rs.getString("UserName");
                }
                else{
                    return null;
    }
    }
    
    
        public ArrayList<SelectItem> getPapers() throws SQLException 
        {
            Statement s = connection.createStatement();
            ArrayList<SelectItem> papers = new ArrayList<SelectItem>();
            ResultSet resultSet = s.executeQuery("SELECT * FROM paper  ");
            while (resultSet.next()) {
            papers.add(new SelectItem(resultSet.getInt("ID"), resultSet.getString("title")));
    }
        return papers;
    }
        
        public ArrayList<SelectItem> getMyAllPapers(int userId) throws SQLException 
        {
            
            Statement s = connection.createStatement();
            
            ArrayList<SelectItem> papers = new ArrayList<SelectItem>();
            ResultSet resultSet = s.executeQuery("SELECT * FROM paper WHERE writer_ID = '" + userId + "'");
            while (resultSet.next()) {
            papers.add(new SelectItem(resultSet.getInt("ID"), resultSet.getString("title")));
    }
        return papers;
    }
        
        

        public int getUserCounter() throws Exception 
        {
            Statement s =connection.createStatement();
            ResultSet rs = s.executeQuery("SELECT * FROM supporttable");
            if(rs.next()){
                return rs.getInt("userCounter");
            }
            else
            {
                return 0;
            }
            
        }
        
        public void increaseUserCounter() throws Exception 
        {
            System.out.printf("getWriterCounter():%d \n",getUserCounter());
            try
            {
                String query = "UPDATE supportTable SET userCounter=1+'"+getUserCounter()+"'";
                PreparedStatement ps = connection.prepareStatement(query);
                ps.executeUpdate();
            }
            catch (Exception ex)
            {
                System.err.println("...........increaseUserCounter ici ");
            }
        }  
        
        public int getPaperCounter() throws Exception 
        {
            Statement s =connection.createStatement();
            ResultSet rs = s.executeQuery("SELECT * FROM supporttable");
            if(rs.next()){
                return rs.getInt("paperCounter");
            }
            else
            {
                return 0;
            }
            
        }
        
        public void increasePaperCounter() throws Exception 
        {
            try
            {
                String query = "UPDATE supportTable SET paperCounter=1+'"+getPaperCounter()+"'";
                PreparedStatement ps = connection.prepareStatement(query);
                ps.executeUpdate();
            }
            catch (Exception ex)
            {
                System.err.println("...........increasePaperCounter ");
            }
        } 
       
}
